SQL Tasks for Day 9 of Internship
1. Sum up the number of students placed with each company to assess their engagement and effectiveness in student placements.
SELECT Company_name, SUM(Students_placed) AS Total_Placed FROM Companies GROUP BY Company_name
2. Identify branches with CGPA average below 7. Display the branch and calculate the average CGPA per branch.
SELECT Branch, AVG(CGPA) AS Avg_CGPA FROM Students GROUP BY Branch HAVING AVG(CGPA) < 7
3. Display the company ID and count the distinct job roles offered by each company.
SELECT CID, COUNT(DISTINCT Job_role) AS Total_Job_Roles FROM Students GROUP BY CID
4. Calculate the total number of students placed by all companies.
SELECT SUM(Students_placed) AS Total_Students_Placed FROM Companies
5. Calculate the average CGPA for each branch to identify trends and areas where academic support may be needed.
SELECT Branch, AVG(CGPA) AS Avg_CGPA FROM Students GROUP BY Branch
6. Calculate the total subject hours for all subjects combined.
SELECT SUM(Hours) AS Total_Subject_Hours FROM Subjects
7. Calculate the total Test2 scores for all students across all subjects.
SELECT SUM(Test2) AS Total_Test2_Scores FROM Marks
8. Count the number of lecturers in each college to manage faculty development programs.
SELECT College, COUNT(LID) AS Total_Lecturers FROM Lecturers GROUP BY College
9. Count the number of students in each branch categorized by gender.
SELECT Branch, Gender, COUNT(*) AS Total_Students FROM Students GROUP BY Branch, Gender
10. Count the number of subjects offered in each semester.
SELECT Semester, COUNT(*) AS Subjects_Count FROM Subjects GROUP BY Semester
11. Identify job roles with an average package less than 500000.
SELECT Job_role, AVG(Package) AS Avg_Package FROM Students GROUP BY Job_role HAVING AVG(Package) < 500000
12. Identify companies that have not offered a job role in the last year.
SELECT DISTINCT Company_name FROM Companies WHERE Company_name NOT IN (SELECT DISTINCT Company_name FROM Students WHERE Year = 2023)
13. Identify the subjects with more than 60 hours of total study time.
SELECT Subject_name, SUM(Hours) AS Total_Hours FROM Subjects GROUP BY Subject_name HAVING SUM(Hours) > 60
14. List the students who have a CGPA greater than 8 and were placed.
SELECT Name FROM Students WHERE CGPA > 8 AND Placed = 'Yes'
15. Get the list of students who have not appeared for Test1 and Test2.
SELECT Name FROM Students WHERE Test1 IS NULL AND Test2 IS NULL